import sys
import pandas as pd
import time
import random
import webbrowser
import pprint
import warnings
import tushare as ts
pro = ts.pro_api()
import akshare as ak

sys.path.extend([".//date", ".//futuredata", ".//spotdata", ".//database", ".//dataprocessor", ".//fundamentaldata"])
from DateModules import *
from GetTodayFut import *
from GetIndice import *
from SingleDayModule import *
from SpotMonitor import *
from spec_class import Spec
from SqlWizard import *
from Fundamentals import *

from CmnFuncs import date_checker_1, date_checker_2, date_checker_3
from Indicators import *
from Crudes import run_crude
from Olefin import run_olefin
from Poly import run_poly
from OtherChems import run_other_chems
from Meals import run_meal
from Oils import run_oil
from OtherAgris import run_other_agris
from Blacks import run_black
from OtherAlloy import run_other_alloy
from Metals import run_metal
from Indice import run_indice_charts

warnings.filterwarnings('ignore')

list_shfe = ['铜', '螺纹钢', '锌', '铝', '黄金', '天然橡胶', '铅', '白银', '沥青', '热卷', '镍', '锡', '纸浆', '不锈钢', '燃料油']
list_dce = ['棕榈油', 'PVC', '聚乙烯', '豆一', '豆粕', '豆油', '玉米', '焦炭', '焦煤', '铁矿石', '鸡蛋', '聚丙烯', '玉米淀粉', '苯乙烯', '乙二醇', '液化石油气']
list_czce = ['PTA', '白糖', '棉花', '菜籽油', '玻璃', '菜籽粕', '硅铁', '锰硅', '甲醇', '动力煤', '棉纱', '尿素', '纯碱']
list_ine = ['沪原油', '20号胶']

my_watchlist_specs = list_shfe + list_dce + list_czce + list_ine

watchlist_dict = {}
for k, v in enumerate(my_watchlist_specs):
    watchlist_dict[k] = v

pprint.pprint(watchlist_dict)

my_indice_list = ['NHCI', 'NHECI', 'NHFI', 'NHAI', 'NHNFI', 'NHPMI']
# 因为以DataFrame格式写入sql表格时，Primary Key会产生重复冲突，所以写入只可以时间顺序写入，而不可以出现重复的时间
# 比如上一次写入到2019-10-01，则下一次必须从10-02开始


def run_indice(issue_dates):
    # df = pro.index_daily(ts_code='CU.NH', start_date='20180101', end_date='20181201')
    strt = dig_date(issue_dates[0])
    ed = dig_date(issue_dates[-1])
    count_indice_writed = 0
    count_indice_overlapped = 0
    for index in my_indice_list:
        index_data = get_index(index, strt, ed)
        if index_data.empty:
            print("Index Data Warning:", index, " empty dataframe detected...")
        index_data.set_index('trade_date', inplace=True)
        time.sleep(random.randint(1, 3))
        try:
            indice_to_sql(index_data, index)  # 将指数数据各品种的分表写入数据库
        except:
            count_indice_overlapped += 1
        else:
            count_indice_writed += 1
        print(index, 'index data collected...')

    print("Indice Data Collected...{} written, {} overlapped dismissed.".format(count_indice_writed, count_indice_overlapped))


def run_spot(issue_dates, x=0):
    # 现货数据部分
    packs = []
    for date in issue_dates:
        udate = url_date(date)  # 返回时间格式'%Y-%m-%d', url_date
        single_day_spot_pack = get_today_data(udate)
        time.sleep(random.randint(1, 2))
        packs.append(single_day_spot_pack)

    # 如果有多个日期的现货数据,则用pd.concat将他们按照列合并在一起成为总表, 否则,就取唯一的那个DataFrame作为总表
    if len(packs) == 1:
        whole_spot_pack = packs[0]
    else:
        whole_spot_pack = pd.concat(packs, sort=False)

    # 从现货数据总表中提取各个品种的分表
    count_spot_writed = 0
    count_spot_overlapped = 0
    for n in my_watchlist_specs[x:]:
        piece = get_piece(n, whole_spot_pack)
        if piece.empty:
            print("Spot Data Warning:", n, " empty spot dataframe detected...")
        # 在这里对获取到的现货数据修饰一下, 因为一个品种一个表,所以index设为时间
        piece.set_index('date', inplace=True)
        sp = Spec(n)
        sbol_p = sp.symbol
        try:
            spot_to_sql(piece, sbol_p) # 将现货数据各品种的分表写入数据库
        except:
            count_spot_overlapped += 1
        else:
            count_spot_writed += 1
        print(n, 'spot data collected...')

    print("Spot Data Collected...{} written, {} overlapped dismissed.".format(count_spot_writed, count_spot_overlapped))


def run_fut_indice(issue_dates):
    # 期货指数数据
    st = dig_date(issue_dates[0])
    ed = dig_date(issue_dates[-1])
    exchange_list = ['SHFE', 'CZCE', 'DCE']
    exc_dict = {'SHFE': list_shfe, 'CZCE': list_czce, 'DCE': list_dce}
    fut_index_overlapped = 0
    fut_index_written = 0
    for exc in exchange_list:
        fi_loopcount = 0
        while True:
            try:
                bulk_df = ak.get_futures_daily(start_day=st, end_day=ed, market=exc, index_bar=True)
            except:
                print("Fetching Error Occurred: Trying again for future index data {}".format(exc))
                fi_loopcount += 1
                if fi_loopcount > 10:
                    print("Fetching Error Occurred: Failed future index data {}".format(exc))
                    raise ConnectionError
                else:
                    continue
            else:
                break

        if bulk_df is not None:
            bulk_df = bulk_df[bulk_df['symbol'].str.len() < 5]
            bulk_df.set_index('symbol', inplace=True)
            for i in exc_dict[exc]:
                spec = Spec(i)
                symbol = spec.symbol
                code = symbol + '99'
                try:
                    df = pd.DataFrame(bulk_df.loc[code])
                except:
                    df = False
                    print("Future index data fetching error: {} data not found in returned Data".format(code))
                else:
                    if len(df.columns.to_list()) == 1:
                        df = df.T
                    else:
                        pass
                    df.set_index('date', inplace=True)
                    df.drop(df.columns[[0, 1, 2, 6, 7, 8, 9]], axis=1, inplace=True)
                    try:
                        fut_index_to_sql(df, symbol)
                    except:
                        fut_index_overlapped += 1
                        print("Future index data Write-in error: {} write-in failed.".format(code))
                    else:
                        fut_index_written += 1
                        print("{} index collected.".format(code))
        else:
            print("Future index data empty: {}".format(exc))
            continue

    print("Future Index Data Collected...{} written, {} overlapped dismissed.".format(fut_index_written,
                                                                                      fut_index_overlapped))


def run_ry(issue_dates, x=0):
    # 展期收益率数据
    st = dig_date(issue_dates[0])
    ed = dig_date(issue_dates[-1])
    ry_overlapped = 0
    ry_written = 0
    for n in my_watchlist_specs[x:]:
        spec = Spec(n)
        symbol = spec.symbol
        ry_loopcount = 0
        while True:
            try:
                rydf = ak.get_roll_yield_bar(type_method="date", var=symbol, start_day=st, end_day=ed, plot=False)
            except:
                print("Fetching Error Occurred: Trying again for roll yield data {}".format(n))
                ry_loopcount += 1
                if ry_loopcount > 10:
                    print("Fetching Error Occurred: Failed roll yield data {}".format(n))
                    raise ConnectionError
                else:
                    continue
            else:
                break

        try:
            ry_to_sql(rydf, symbol)
        except:
            ry_overlapped += 1
            print("Roll Yield Write-in Error: {} write-in failed.".format(symbol))
        else:
            ry_written += 1
            print("{} roll yield collected.".format(symbol))

    print("Roll Yield Data Collected...{} written, {} overlapped dismissed.".format(ry_written, ry_overlapped))


def run_fut(issue_dates, x=0):
    # 期货数据部分
    strt = dig_date(issue_dates[0])
    ed = dig_date(issue_dates[-1])
    # 获取到适用于tushare的时间后,进入for循环列表,而后循环合约,而后带入时间得到数据
    count_fut_writed = 0
    count_fut_overlapped = 0
    for n in my_watchlist_specs[x:]:
        sf = Spec(n)
        symb = sf.symbol
        contracts = get_iter_num(n)
        for contract in contracts:
            exc = sf.check_exchange()
            code = sf.get_code(contract)
            hld_loopcount = 0
            while True:
                try:
                    bulk_df = get_bulk_hld_data(code, exc)
                except:
                    print("Fetching Error Occurred: Trying again for bulk holding data {}".format(exc))
                    hld_loopcount += 1
                    if hld_loopcount > 10:
                        print("Fetching Error Occurred: Failed bulk holding data {}".format(exc))
                        raise ConnectionError
                    else:
                        continue
                else:
                    break
            hld_pack = []
            for d in issue_dates:
                ddt = dig_date(d)
                hld_single_list = get_hld_single(bulk_df, ddt)
                if len(hld_single_list) == 0:
                    pass
                else:
                    hld_pack.append(hld_single_list)
            if len(hld_pack) == 0:
                result_pack = None
            else:
                result_pack = hld_pack
            result_df = pd.DataFrame(data=result_pack, columns=['trade_date', 'informed', 'uninformed', 'lsdexlite'])
            result_df.set_index('trade_date', inplace=True)
            if result_df.empty:
                print("Future Data Warning: ", n, code, " empty spyderNet dataframe detected...")
            fut_loopcount = 0
            while True:
                try:
                    single_data = get_single_data(n, contract, strt, ed)
                except:
                    time.sleep(15)
                    print("Fetching Error Occurred: Trying again for future trading data {}-{}".format(n, contract))
                    fut_loopcount += 1
                    if fut_loopcount > 10:
                        print("Fetching Error Occurred: Failed future trading data {}-{}".format(n, contract))
                        raise ConnectionError
                    else:
                        continue
                else:
                    break
            if single_data.empty:
                print("Future Data Warning: ", n, code, " empty Trade dataframe detected...")

            time.sleep(random.randint(1, 2))
            single_data.set_index('trade_date', drop=False, inplace=True)
            single_full_data = pd.concat([single_data, result_df], axis=1, sort=False)
            single_full_data.set_index('trade_date', inplace=True)
            tc = sf.get_code(contract)
            try:
                future_to_sql(single_full_data, tc) # 将每一个合约数据写入数据库
            except:
                count_fut_overlapped += 1
            else:
                count_fut_writed += 1
            print(n, contract, 'future data collected...')

    print("Future Data Collected...{} written, {} overlapped dismissed.".format(count_fut_writed, count_fut_overlapped))


def run_spder_mod(issue_dates, x=0):
    '''蛛网模型整体持仓数据'''
    for n in my_watchlist_specs[x:]:
        spec = Spec(n)
        sym = spec.symbol
        exc = spec.check_exchange()
        spy_loopcount = 0
        while True:
            try:
                bulk_df = get_bulk_hld_data(sym, exc)
            except:
                print("Fetching Error Occurred: Trying again for bulk spyder data {}".format(exc))
                spy_loopcount += 1
                if spy_loopcount > 10:
                    print("Fetching Error Occurred: Failed bulk spyder data {}".format(exc))
                    raise ConnectionError
                else:
                    continue
            else:
                break
        sig_pack = []
        for date in issue_dates:
            ddate = dig_date(date)
            signal_list = get_holding(bulk_df, sym, exc, ddate)
            if len(signal_list) == 0:
                pass
            else:
                sig_pack.append(signal_list)
        if len(sig_pack) == 0:
            result_pack = None
        else:
            result_pack = sig_pack
        sig_df = pd.DataFrame(data=result_pack,
                              columns=['trade_date', 'symbol', 'informed', 'uninformed', 'lsdexlite'])
        sig_df.set_index('trade_date', inplace=True)
        if sig_df.empty:
            print("SpiderNet Data Warning:", n, " empty spiderNet dataframe detected...")
        print('{} SpiderNet writing.'.format(sym))
        try:
            spdrdx_to_sql(sig_df, sym)
        except:
            print('Spider Net Index Write-in Error: {}'.format(sym))
        else:
            print('{} SpiderNet written.'.format(sym))
            pass


def run_stargaze():
    days = {'1': '周一',
            '2': '周二',
            '3': '周三',
            '4': '周四',
            '5': '周五',
            '6': '周六',
            '7': '周日',
            }
    today = pd.datetime.now()
    todate = today.date()
    w = pd.datetime.isoweekday(today)
    weekday = days[str(w)]
    print("Project StarGaze ver.: 2.1    Author: Daniel Zhang\n"
          "------------------------------------------------------------\n"
          "欢迎使用<观星>期货数据收集及监测程序\n"
          "今天是{}, {}\n\n"
          "使用提示：\n\n"
          "收集数据：1. 交易数据收集频率为每交易日，宏观数据收集频率为每周周一至周末\n\n"
          "----------2. 由于主键缘故，出现数据重叠会造成收集到的dataframe无法录入数据库或报错等情况。\n\n"
          "----------3. 由于数据来源问题，目前PMI分项数据需要到统计局官网手动统计，\n\n"
          "-------------布伦特及WTI数据需要到investing.com下载并保存至petrodata文件夹内\n\n"
          "月份更替：进入1月、5月、6月、9月、10月、12月时需要先建立新的sql表，再收集数据\n\n"
          "------------------------------------------------------------\n".format(todate, weekday))
    nowdate = pd.datetime.now().month
    if nowdate in [1, 5, 6, 9, 10, 12]:
        print(" ===============================================================\n"
              "|*****目前月份属于合约换月月份，请确认是否已经建立新的SQL表*****|\n"
              " ===============================================================\n")
    my_choice = input("------------------------------------------------------------\n"
                      "1. Collect Data...\n2. Review Data Charts...\nPress Any to Quit..\n"
                      "------------------------------------------------------------\n")
    if my_choice == '1':
        # 因为最好把表中的数据类型提前规定好,所以这里先创建所有的品种的:现货, 期货合约 在Mysql内对应的表, 无需二次运行此部分代码
        # 这里只创建现货、期货及指数表
        checkr = input("------------------------------------------------------------\n"
                       "Press 'y' if you want to rebuild all tables\n"
                       "------------------------------------------------------------\n")
        if checkr.lower() == 'y':
            for index in my_indice_list:
                create_indice_table(index)
            for n in my_watchlist_specs:
                i = Spec(n)
                isymbol = i.symbol
                create_spot_table(isymbol)
                icountract = get_iter_num(n)
                for c in icountract:
                    code = i.get_code(c)
                    create_fut_table(code)
                create_spdrdex_table(isymbol)
                create_fut_index_table(isymbol)
                create_ry_table(isymbol)
        else:
            pass

        # 循环以收集输入的日期,并确认日期格式正确, 两个日期格式都正确则退出循环, 日期分发列表赋给 issue_dates
        while True:
            date_collector = collector()
            start = date_collector[0]
            end = date_collector[1]
            check_start = check_format(start)
            check_end = check_format(end)

            if check_start and check_end:
                issue_dates = date_issuer(start, end)
                break
            else:
                print('###Incorrect date format. Please try again...###')

        while True:
            count_empty = 0
            for exc in ['CZCE', 'DCE', 'SHFE']:
                testdf = pro.fut_holding(trade_date=dig_date(issue_dates[-1]), exchange=exc)
                if testdf.empty:
                    count_empty += 1
                    print("{} Data Empty...".format(exc))
                else:
                    continue
            if count_empty != 0:
                print("\nCurrently holding data is not ready yet, {} empty detected. "
                      "Please collect future data later...\n".format(count_empty))
                rps = input("\nPress Y to recheck holding data accessibility...\n"
                            "Or press S if you would like to skip for other Data COLLECTION...\n"
                            "Press Any to Quit...")
                if rps.lower() == 'y':
                    continue
                elif rps.lower() == 's':
                    break
                else:
                    quit()
            else:
                print("\nHolding data is ready for this collection.\n")
                break

        while True:
            count_empty = 0
            for n in ['PTA', '豆粕', '螺纹钢']:
                spec = Spec(n)
                contract = get_iter_num(n)
                code = spec.get_code(contract[1]) + "." + spec.check_exchange()
                futtest = pro.fut_daily(ts_code=code, start_date=dig_date(issue_dates[-1]))
                if futtest.empty:
                    print("{} Data Empty...".format(spec.check_exchange()))
                    count_empty += 1
                else:
                    continue
            if count_empty != 0:
                print("\nCurrently contracts trading data is not ready yet, {} empty detected. "
                      "Please collect future data later...\n".format(count_empty))
                rps = input("\nPress Y to recheck contracts trading data accessibility...\n"
                            "Or press S if you would like to skip for other DATA COLLECTION...\n"
                            "Press Any to Quit...")
                if rps.lower() == 'y':
                    continue
                elif rps.lower() == 's':
                    break
                else:
                    quit()
            else:
                print("\nContracts trading data is ready for this collection.\n")
                break

        while True:
            count_empty = 0
            for exc in ['CZCE', 'DCE', 'SHFE']:
                testbulkdf = ak.get_futures_daily(start_day=dig_date(issue_dates[0]), end_day=dig_date(issue_dates[-1]),
                                                  market=exc, index_bar=True)
                if testbulkdf is None:
                    print("{} Data Empty...".format(exc))
                    count_empty += 1
                else:
                    continue
            if count_empty != 0:
                print("\nCurrently Future Index data is not ready yet, {} empty detected. "
                      "Please collect future data later...\n".format(count_empty))
                rps = input("\nPress Y to recheck Future Index data accessibility...\n"
                            "Or press S if you would like to skip for other Data COLLECTION...\n"
                            "Press Any to Quit...")
                if rps.lower() == 'y':
                    continue
                elif rps.lower() == 's':
                    break
                else:
                    quit()
            else:
                print("\nFuture Index data is ready for this collection.\n")
                break

        choice = input('------------------------------------------------------------\n'
                       'What data do you want to collect? '
                       '\n1. Spot Data...\n2. Future Data...\n3. Indice Data...\n4. Update Monitor...'
                       '\n5. SpiderNet Data...'
                       '\n6. Roll Yield Data...'
                       '\n7. Future Index Data...'
                       '\n8. All Trade Data...'
                       '\n9. Fundamental Data...\nPRESS ANY TO QUIT'
                       '\nYour choice: \n'
                       '------------------------------------------------------------\n')
        conticheck = input("Do you need to pick up from last time? Please enter where you want to start(Number only):\n"
                           "Or Press 'Enter' to start from beginning:")
        if conticheck:
            x = int(conticheck)
        else:
            x = 0
        if choice == '1':
            run_spot(issue_dates, x)
        elif choice == '2':
            run_fut(issue_dates, x)
        elif choice == '3':
            run_indice(issue_dates)
        elif choice == '4':
            monitor_data()
        elif choice == '5':
            run_spder_mod(issue_dates, x)
        elif choice == '6':
            run_ry(issue_dates, x)
        elif choice == '7':
            run_fut_indice(issue_dates)
        elif choice == '8':
            run_spot(issue_dates, x)
            run_fut(issue_dates, x)
            run_indice(issue_dates)
            run_spder_mod(issue_dates, x)
            run_fut_indice(issue_dates)
            run_ry(issue_dates, x)
            monitor_data()
        elif choice == '9':
            pull_cpi()
            pull_ppi()
            pull_money_supply()
            pull_rrr()
            pull_shibor(dig_date(issue_dates[0]), dig_date(issue_dates[-1]))
            pull_lpr(dig_date(issue_dates[0]), dig_date(issue_dates[-1]))
            pull_leverage()
            pull_us_ir()
            pull_us_nf()
            pull_us_ur()
            pull_us_eia()
        else:
            pass

        print('--------------------------------\n###All done! Have a nice day!###\n--------------------------------')

    elif my_choice == '2':

        question = input("------------------------------------------------------------\n"
                         "Do you want to reform data charts? y/n\nPress Any to Quit.\n"
                         "------------------------------------------------------------\n")
        if question.lower() == 'y':
            with open('ratioup.csv', mode='w+', encoding='gbk') as f:
                f.write('name,cur_value,position,corr\n')
            with open('ratiodown.csv', mode='w+', encoding='gbk') as f:
                f.write('name,cur_value,position,corr\n')
            with open('difup.csv', mode='w+', encoding='gbk') as f:
                f.write('name,cur_value,position,corr\n')
            with open('difdown.csv', mode='w+', encoding='gbk') as f:
                f.write('name,cur_value,position,corr\n')
            spot_monitor()
            run_indice_charts()
            run_crude()
            run_olefin()
            run_poly()
            run_other_chems()
            run_meal()
            run_oil()
            run_other_agris()
            run_black()
            run_other_alloy()
            run_metal()
            show_fundamentals()
            # try:
            run_indicator(my_watchlist_specs, 1)
            # except Exception as error:
            #     print("Indicator Sector Error Occurred: %s" % error)
            # else:
            #     pass


        elif question.lower() == 'n':
            pass
        else:
            quit()

        pagepool = ['宏观基本面数据',
                    '南华商品指数',
                    '价格监控页面',
                    '交易指标',
                    '原油链',
                    '聚酯链',
                    '聚烯烃链',
                    '其他化工品',
                    '油脂链',
                    '粕类饲料链',
                    '其他农副',
                    '黑色链',
                    '其他合金',
                    '贵金属及有色金属']

        chromepath = r"..\application\chrome.exe"
        webbrowser.register('chrome', None, webbrowser.BackgroundBrowser(chromepath))

        for page in pagepool:
            pageaddress = "file:///../{}.html".format(page)
            webbrowser.get('chrome').open(pageaddress, new=2)
            time.sleep(1)


if __name__ == '__main__':
    while True:
        run_stargaze()
        ot = input("------------------------------------------------------------\n"
                   "Program Stargaze finished, next: \n1. Press '1' to rerun Program\n2. Press Any to quit\n"
                   "------------------------------------------------------------\n")
        if ot == '1':
            pass
        else:
            break
